﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using CommunitySystemModel;
using CommunitySystem.BO;
using System.Collections.ObjectModel;
using System.Data;

namespace CommunitySystem
{
   public class JieYongBO
    {
       public bool SaveJY(Emp_JYModel entity, bool isAdd)
       {
           string sql = string.Empty;
           if (isAdd)
               sql = string.Format(@"INSERT INTO Emp_JY
           ([TLDate],[Name],[EID],[OldKS],[OldZW],[BeginDate],[EndDate],[NewDW],[NewKS],[NewZW],[Status],[CreateUser],[CreateDate],orgid)
           VALUES
           ('{0}','{1}',{2} ,'{3}' ,'{4}','{5}' ,'{6}','{7}','{8}','{9}','{10}','{11}','{12}',(select top 1 ID from OrgForHR where Name='{7}')) select @@IDENTITY"
                   , entity.TLDate, entity.Name, entity.EID, entity.OldKS, entity.OldZW,Convert.ToDateTime(entity.BeginDate), null, entity.NewDW, entity.NewKS, entity.NewZW, entity.Status, entity.CreateUser, entity.CreateDate);
           else
               sql = string.Format(@"UPDATE Emp_JY
      SET [TLDate] ='{0}',[Name] ='{1}',[EID] = {2},[OldKS] = '{3}',[OldZW] = '{4}',[BeginDate] ='{5}',[EndDate] = '{6}',[NewDW] = '{7}',[NewKS] = '{8}'
      ,[NewZW] = '{9}',[Status] = '{10}',[CreateUser] = '{11}',[CreateDate] = '{12}',orgid=(select top 1 ID from OrgForHR where Name='{7}')
      WHERE id={13} select {13}", entity.TLDate, entity.Name, entity.EID, entity.OldKS, entity.OldZW, entity.BeginDate, null, entity.NewDW, entity.NewKS, entity.NewZW, entity.Status, entity.CreateUser, entity.CreateDate, entity.Id);

          
          int result = Convert.ToInt32(AccessHelper.GetInstance().ExcuteDataSet(sql).Tables[0].Rows[0][0]);
          if (result > 0)
          {
            //简历：新增 确认后，简历自动增加一条内容：
            //开始日期           结束日期         所在单位        担任职务     证明人   是否为期间
            //提取自**起时间     暂时为空      借掉至+单位名称    科室+职务                是
              string sql1 = string.Empty;
              if (isAdd)
                  sql1 = string.Format(@"update Emp_Resume set EndDate='{1}',JyId={4} where (EndDate='' or EndDate is null) and EmpbasicID={0};
                    insert into Emp_Resume (EmpbasicID,BeginDate,EndDate,Unit,Duty,IsPeriod,JyID)
                    values({0},'{1}',null,'{2}','{3}',1,{4})", entity.EID, Convert.ToDateTime(entity.BeginDate), "借掉至" + entity.NewDW, entity.NewKS + entity.NewZW, result);
              else
                  sql1 = string.Format(@"update Emp_Resume set EndDate='{1}' where  EmpbasicID={0} and jyid={4} and (EndDate!='' or EndDate is not null);
                        update Emp_Resume  set EmpbasicID={0},BeginDate='{1}',EndDate=null,Unit='{2}',Duty='{3}',IsPeriod=1
                        where jyid={4}", entity.EID, Convert.ToDateTime(entity.BeginDate), "借掉至" + entity.NewDW, entity.NewKS + entity.NewZW, result);
              if (!string.IsNullOrEmpty(sql1))
              {
                  if (AccessHelper.GetInstance().ExecuteNonQuery(sql1) > 0)
                      return true;
                  else return false;
              }
              else
                  return true;
          }
          else return false;
       }
       public bool JYJS(Emp_JYModel entity)
       {
           string sql = string.Empty;
           //借用结束
           sql = string.Format(@"update Emp_JY set status='{0}',EndDate='{1}' where id={2};",entity.Status,entity.EndDate,entity.Id);
           sql += string.Format(@"update Emp_Resume set EndDate='{0}' where EmpbasicID={1} and (EndDate='' or EndDate is null) and jyid={2}", entity.EndDate, entity.EID, entity.Id);
           int result = AccessHelper.GetInstance().ExecuteNonQuery(sql);
           if (result > 0)
               return true;
           else return false;
       }
       public bool JYDel(string con)
       {
           string sql = string.Empty;
           sql = string.Format(@"delete emp_jy where id in ({0});
           delete Emp_Resume where jyid in ({0}) and (EndDate='' or EndDate is null);
            update Emp_Resume set EndDate=null where jyid in ({0}) and (EndDate!='' or EndDate is not null)", con);
           int result = AccessHelper.GetInstance().ExecuteNonQuery(sql);
           if (result > 0)
               return true;
           else return false;
       }
       public ObservableCollection<Emp_JYModel> GetJY(string con, int index, int pagesize)
       {
           ObservableCollection<Emp_JYModel> rights = new ObservableCollection<Emp_JYModel>();
           string sql = string.Format(@"select top({0}) ROW_NUMBER() over(order by ID desc) as Sequence,IsSelected='false'
                                        ,* from
                                        (
                                        select ROW_NUMBER() over(order by ID desc) as rowsID,* from Emp_JY emp where 1=1 {2}
                                        ) a where a.rowsID>=
                                        (
                                        SELECT ISNULL(MAX(rowsID),0) FROM 
                                        (
                                        select top({0}*({1}-1)+1) * from
                                        (
                                        select ROW_NUMBER() over(order by ID desc) as rowsID,* from Emp_JY emp where 1=1 {2}
                                        )b
                                        )c
                                        ) order by CreateDate desc", pagesize, index, con);
           DataSet ds = AccessHelper.GetInstance().ExcuteDataSet(sql);
           foreach (DataRow dr in ds.Tables[0].Rows)
           {
               rights.Add(Emp_JYModel.FillEntity(dr));
           }
           return rights;
       }
       public int GetJYCount(string con)
       {
           string sql = @"select  COUNT(id) from Emp_JY emp where 1=1";
           if (!string.IsNullOrEmpty(con))
           {
               sql += con;
           }

           DataSet ds = AccessHelper.GetInstance().ExcuteDataSet(sql);
           return Convert.ToInt32(ds.Tables[0].Rows[0][0]);
       }
    }
}
